Connecting to Postgres...

Postgres London
2022-06-24

Who am I

• Lætitia Avrot
• PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Field CTO
• @l_avrot
• mydbanotebook.org

Connecting

• How to connect
• How to authenticate
• Postgres security
• How to troubleshoot connection problems

Connecting

            $ psql --host localhost --port 5432 --username laetitia \
  --dbname test
  psql (15devel)
  Type "help" for help.

  test=# 

Host

$ psql --host localhost

$ psql -h localhost

psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=localhost
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5432".

Host

$ psql -h /tmp

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5432".

Port

$ psql --port 5433

$ psql -p 5433

psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5433".

Port

$ export PGPORT=5433
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5433".

Port

$ export PGPORT=
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5432".

User

$ psql --username test

$ psql -U test

psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "test"
  via socket in "/tmp" at port "5432".

User

$ export PGUSER=test
  $ psql

  psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "test"
  via socket in "/tmp" at port "5432".

User

$ export PGUSER=
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5432".

Database

$ psql test

$ psql --dbname test

$ psql -d test

psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "laetitia"
  via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=test
  $ psql

  psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "laetitia"
  via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=
  $ psql

  psql (15devel)
  Type "help" for help.

  laetitia=# \conninfo
  You are connected to database "laetitia" as user "laetitia"
  via socket in "/tmp" at port "5432".

Database

$ psql postgresql://laetitia@localhost:5433/test

  psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "laetitia"
  on host "localhost" (address "::1") at port "5433".

Database

$ psql "user=laetitia host=localhost port=5433 dbname=test"

  psql (15devel)
  Type "help" for help.

  test=# \conninfo
  You are connected to database "test" as user "laetitia"
  on host "localhost" (address "::1") at port "5433".

Service file

$ cat ~/.pg_service.conf
  [mydb]
  host=localhost
  # Port is same as default but I could use port=5432
  user=test
  dbname=laetitia

            psql service=mydb
  psql (15devel)
  Type "help" for help.

  laetitia=> \conninfo
  You are connected to database "laetitia" as user "test"
  on host "localhost" (address "::1") at port "5432".

Authentication

• pg_hba file
• Chapter 21 of Postgres documentation

pg_hba

connection-type  database  user  auth-method  [auth-options]

connection-type  database  user  address  auth-method  [auth-options]

• Order matters
• Try to keep it clean
• Don't open to everyone

Type of connection

• local
• host
• hostssl
• others

Database

• database name
• "all"
• replication

User

• user name
• "all"
• replication

Address

• Ip (v4, v6)
• With mask (or mask length)
• hostname
• all

Authentication method

• scram-sha-256
• md5
• cert
• ldap
• ident
• trust
• others

Troubleshooting

Troubleshooting

Is it running?

For Debian-based (as root):

pg_lsclusters

For other systemD systems (as root):

systemctl status postgresql

Is it running?

For Windows:

Check the postgresql service

For others:

pg_ctl status -D [PGDATA]

For all except Windows:

pgrep -u postgres -fa -- -D

How to start it

For Debian-based (as root):

pg_ctlcluster 14 [name] start

For other systemD systems (as root):

systemctl start postgresql

Is it running?

For Windows:

Start the postgresql service

For others (as postgres):

pg_ctl start -D [PGDATA]

Troubleshooting

What is the port number?

For all except Windows (as root or postgres):

sed -n 4p [PGDATA]/postmaster.pid

or

For Debian-based (as root):

pg_lsclusters

Troubleshooting

Troubleshooting

listen_addresses

• default: localhost only
• needs to be open (or not)
• * is not as insecure as you might think

Troubleshooting

Is your port open?

(echo >/dev/tcp/[host]/[port]) &>/dev/null && \
          echo "open" || echo "close"

Troubleshooting

Troubleshooting

Where are my logs

Contacting the community

• the pgsql-bugs mailing list
• IRC
• Slack
• Bug report form

Are you confused?